#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   curd.py
@Time    :   2021/02/08 00:12:14
@Author  :   superjin
@Version :   1.0
'''

from hashlib import new
from os import name
from typing import Any, Optional, List
from sqlalchemy import func,and_
from sqlalchemy.orm import Session
from models.user import User
from models.participant import Participant
from models.classroom import Classroom
from models.reservation import Reservation
from core.schemas.user import UserCreate, UserUpdate
from core.security import get_password_hash, verify_password


class CRUDUser:
    def change_password(self, db: Session, uid:str, password: str) -> Optional[User]:
        res = db.query(User).filter_by(user_id=uid).update(
            {"password": get_password_hash(password) })
        db.commit()
        return res

    def authenticate(self, db: Session, username: str, password: str) -> Optional[User]:
        user = self.get_user_by_username(db, username=username)
        #print(user)
        if not user:
            return None
        if not verify_password(password, user.password):
            return None
        return user

    def get_users(self, db: Session, page:int, pageSize:int):
        # 使用数据库进行分页
        data = db.query(User).offset((page-1)*pageSize).limit(pageSize).all()
        total = db.query(func.count(User.user_id)).scalar()
        return data,total
    
    def create(self, db: Session, new_user: UserCreate) :
        user = User(new_user.username, new_user.name, get_password_hash(new_user.password), 
                        new_user.phone,new_user.role, new_user.email, )
        db.add(user)
        db.commit()
        db.refresh(user)
        return user
    def get_user_by_username(self, db: Session, username: str) -> Optional[User]:
        res = db.query(User).filter_by(
            username=username).first()
        return res
    def get_user_by_uid(self, db: Session, user_id: int) -> Optional[User]:
        res = db.query(User).filter_by(
            user_id=user_id).first()
        return res
    def update_user(self, db: Session, user_id, profile: UserUpdate):
        update_data = profile.dict(exclude_unset=True)
        res = db.query(User).filter_by(user_id=user_id).update(update_data)
        db.commit()
        return res

    def update_avator(self, db: Session, user_id, pic_id:str):
        res = db.query(User).filter_by(user_id=user_id).update(
                    {"pic_url":pic_id})
        db.commit()
        return res

    def statistics(self,db:Session):
        sql = """
              SELECT 
                  u.username,
                  COUNT(p.reservation_id) AS total_count,
                  SUM(TIMESTAMPDIFF(SECOND, p.check_in, LEAST(r.end_time, ADDDATE(p.check_in, INTERVAL 23 HOUR)))) / 3600 AS total_time,
              	  ROW_NUMBER() OVER (ORDER BY total_time desc) AS ranking
              FROM 
                  participant p
              JOIN 
                  reservation r ON p.reservation_id = r.reservation_id
              JOIN 
                  user u ON p.user_id = u.user_id
              WHERE 
                  p.check_in IS NOT NULL
                  AND r.start_time >= NOW() - INTERVAL 1 MONTH
              GROUP BY 
                  u.user_id
              ORDER BY 
                  total_time desc;
              """
        result = db.execute(sql)
        data = [] 
        for i in result.fetchall():
            dic = {}
            index = 0
            for j in result.keys():
                dic[j] = i[index]
                index += 1
            data.append(dic)
        return data
    
    def self_statitics(self,db:Session,user_id):
        sql = """
              SELECT 
                  COUNT(DISTINCT r.reservation_id) AS reservations,   
                  COUNT(DISTINCT p.reservation_id) AS participations,     
                  COUNT(DISTINCT CASE WHEN p.check_in IS NOT NULL THEN p.reservation_id END) AS attended,  
                  COUNT(DISTINCT CASE WHEN p.check_out IS NULL THEN p.reservation_id END) AS absent  
              FROM 
                  user u
              LEFT JOIN 
                  reservation r ON u.user_id = r.creator_id
              LEFT JOIN 
                  participant p ON u.user_id = p.user_id
              WHERE 
                  u.user_id = {} -- 在这里用你实际传递的 user_id
              """
        result = db.execute(sql.format(user_id))
        data = [] 
        for i in result.fetchall():
            dic = {}
            index = 0
            for j in result.keys():
                dic[j] = i[index]
                index += 1
            data.append(dic)
        return data[0]

    def participation_statitics(self,db:Session,user_id):
        sql = """
              SELECT 
                  date_series.date AS date,
                  COUNT(DISTINCT CASE WHEN p.check_in IS NOT NULL THEN p.reservation_id END) AS total_attended
              FROM 
                  (
                      SELECT CURDATE() - INTERVAL n DAY AS date
                      FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) AS days
                  ) AS date_series
              LEFT JOIN 
                  reservation r ON DATE(r.start_time) = date_series.date
              LEFT JOIN 
                  participant p ON r.reservation_id = p.reservation_id AND p.user_id = {} AND p.check_in IS NOT NULL
              GROUP BY 
                  date_series.date
              ORDER BY 
                  date_series.date;
              """
        result = db.execute(sql.format(user_id))
        # data = [] 
        # for i in result.fetchall():
        #     dic = {}
        #     index = 0
        #     for j in result.keys():
        #         dic[j] = i[index]
        #         index += 1
        #     data.append(dic)
        rows = result.fetchall()

        # 转换数据格式
        data = [{
            "x": row['date'],  # 使用日期字段
            "y": row['total_attended']
        } for row in rows]
        return data
user = CRUDUser()
